package com.hotelManager.www.dao;

import com.hotelManager.www.po.Order;
import com.hotelManager.www.util.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.time.LocalDateTime;
import java.math.BigDecimal;

public class OrderDao {
    
    public void save(Order order) throws SQLException {
        String sql = "INSERT INTO orders (user_id, shop_id, room_id, total_amount, status, create_time, shop_name, uni_room_code) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            pstmt.setInt(1, order.getUserId());
            pstmt.setInt(2, order.getShopId());
            pstmt.setInt(3, order.getRoomId());
            pstmt.setBigDecimal(4, order.getTotalAmount());
            pstmt.setString(5, order.getStatus());
            pstmt.setTimestamp(6, Timestamp.valueOf(order.getCreateTime()));
            pstmt.setString(7, order.getShopName());
            pstmt.setString(8, order.getUniRoomCode());
            
            pstmt.executeUpdate();
            
            try (ResultSet rs = pstmt.getGeneratedKeys()) {
                if (rs.next()) {
                    order.setId(rs.getInt(1));
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    
    public void update(Order order) throws SQLException {
        String sql = "UPDATE orders SET status = ? WHERE id = ?";
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, order.getStatus());
            pstmt.setInt(2, order.getId());
            pstmt.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public boolean updateResult(Order order) throws SQLException {
        String sql = "UPDATE orders SET status = ? WHERE id = ?";
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, order.getStatus());
            pstmt.setInt(2, order.getId());
            return pstmt.executeUpdate() > 0; // 返回更新是否成功
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    
    public List<Order> findByUserId(Integer userId) throws SQLException {
        String sql = "SELECT * FROM orders WHERE user_id = ? ORDER BY create_time DESC";
        List<Order> orders = new ArrayList<>();
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, userId);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    orders.add(mapResultSetToOrder(rs));
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return orders;
    }
    
    public Order findById(Integer id) throws SQLException {
        String sql = "SELECT * FROM orders WHERE id = ?";
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return mapResultSetToOrder(rs);
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return null;
    }
    
    private Order mapResultSetToOrder(ResultSet rs) throws SQLException {
        Order order = new Order();
        order.setId(rs.getInt("id"));
        order.setUserId(rs.getInt("user_id"));
        order.setShopId(rs.getInt("shop_id"));
        order.setRoomId(rs.getInt("room_id"));
        order.setTotalAmount(rs.getBigDecimal("total_amount"));
        order.setStatus(rs.getString("status"));
        order.setCreateTime(rs.getTimestamp("create_time").toLocalDateTime());
        order.setShopName(rs.getString("shop_name"));
        order.setUniRoomCode(rs.getString("uni_room_code"));
        return order;
    }

    /**
     * 统计指定时间段内完成的订单数量
     */
    public int countCompletedOrders(Connection conn, int shopId, LocalDateTime startTime, LocalDateTime endTime) throws SQLException {
        String sql = "SELECT COUNT(*) FROM orders WHERE shop_id = ? AND status = 'completed' " +
                    "AND complete_time >= ? AND complete_time < ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, shopId);
            stmt.setTimestamp(2, Timestamp.valueOf(startTime));
            stmt.setTimestamp(3, Timestamp.valueOf(endTime));
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1);
                }
            }
        }
        return 0;
    }

    /**
     * 统计指定时间段内完成订单的总金额
     */
    public BigDecimal sumCompletedOrdersAmount(Connection conn, int shopId, LocalDateTime startTime, LocalDateTime endTime) throws SQLException {
        String sql = "SELECT COALESCE(SUM(total_amount), 0) FROM orders WHERE shop_id = ? AND status = 'completed' " +
                    "AND complete_time >= ? AND complete_time < ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, shopId);
            stmt.setTimestamp(2, Timestamp.valueOf(startTime));
            stmt.setTimestamp(3, Timestamp.valueOf(endTime));
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getBigDecimal(1);
                }
            }
        }
        return BigDecimal.ZERO;
    }

    /**
     * 更新订单状态为取消
     */
    public void cancelOrder(Integer orderId, String status, Connection conn) throws SQLException {
        String sql = "UPDATE orders SET status = ? WHERE id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, status);
            pstmt.setInt(2, orderId);
            pstmt.executeUpdate();
        }
    }

    /**
     * 查找指定商店的所有待处理订单
     * @throws Exception 
     */
    public List<Order> findPendingOrdersByShopId(Integer shopId) throws Exception {
        String sql = "SELECT * FROM orders WHERE shop_id = ? AND status = 'pending'";
        List<Order> orders = new ArrayList<>();
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, shopId);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    orders.add(mapResultSetToOrder(rs));
                }
            }
        }
        return orders;
    }

    /**
     * 查找指定用户的订单详细信息
     */
    public List<Order> findOrdersWithDetailsByUserId(Integer userId) throws SQLException {
        String sql = "SELECT o.*, u.username AS user_name, s.name AS shop_name, " +
                     "CONCAT(r.room_code, '-', o.uni_room_code) AS room_number " +
                     "FROM orders o " +
                     "LEFT JOIN users u ON o.user_id = u.id " +
                     "LEFT JOIN shops s ON o.shop_id = s.id " +
                     "LEFT JOIN rooms r ON o.room_id = r.id " +
                     "WHERE o.user_id = ? ORDER BY o.create_time DESC";
        List<Order> orders = new ArrayList<>();
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, userId);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Order order = mapResultSetToOrder(rs);
                    order.setUserName(rs.getString("user_name"));
                    order.setShopName(rs.getString("shop_name"));
                    order.setRoomNumber(rs.getString("room_number"));
                    orders.add(order);
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return orders;
    }

    /**
     * 根据商家ID和订单状态查询订单
     * @param shopId 商家ID
     * @param status 订单状态
     * @return 订单列表
     * @throws Exception 
     */
    public List<Order> findByShopIdAndStatus(Integer shopId, String status) throws Exception {
        List<Order> orders = new ArrayList<>();
        String sql = "SELECT * FROM orders WHERE shop_id = ? AND status = ?";
        
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, shopId);
            pstmt.setString(2, status);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Order order = new Order();
                    order.setId(rs.getInt("id"));
                    order.setUserId(rs.getInt("user_id"));
                    order.setShopId(rs.getInt("shop_id"));
                    order.setTotalAmount(rs.getBigDecimal("total_amount"));
                    order.setStatus(rs.getString("status"));
                    orders.add(order);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return orders;
    }
}

